﻿Imports DTO
Imports System.Data.OleDb
Public Class PhieuGoiTienDAO
    Public Function ThemPhieuGoiTien(ByVal pgtDTO As PhieuGoiTienDTO) As Integer
        Dim sql As String = "Insert Into PhieuGoiTien(MaSo,MaKhachHang,NgayGoi,SoTienGoi) Values(@MaSo,@MaKH,@NgayGoi,@SoTienGoi)"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim para As OleDbParameter
        para = cmd.Parameters.Add("@MaSo", OleDbType.Integer)
        para.Value = pgtDTO.MaSo

        para = cmd.Parameters.Add("@MaKH", OleDbType.Integer)
        para.Value = pgtDTO.MaKH

        para = cmd.Parameters.Add("@NhayGoi", OleDbType.Date)
        para.Value = pgtDTO.NgayGoi.Date

        para = cmd.Parameters.Add("@SoTienGoi", OleDbType.Double)
        para.Value = pgtDTO.SoTienGoi

        Dim kq As Integer = cmd.ExecuteNonQuery()
        con.Close()
        If kq >= 0 Then
            Return 1
        Else
            Return 0
        End If
    End Function
    'Lấy mã gui tien  max
    Public Function LayMaGoiTienMax() As Integer
        Dim sql As String = "Select Max(MaPhieuGoi) From PhieuGoiTien"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim mapg As Integer
        Try
            mapg = cmd.ExecuteScalar
        Catch ex As Exception
            mapg = 0
        End Try
        con.Close()
        Return mapg
    End Function
    'Tìm kiếm theo nhiều tiêu trí
    Public Function BuildQuery(ByVal pgtCrt As PhieuGoiTienCrt, ByVal cn As OleDbConnection) As OleDbCommand
        Dim cmd As New OleDbCommand()
        Dim strDKMaSo = " 1=1 "
        Dim strDKTenKH = " 1=1 "
        Dim strDKSoTienGoi = " 1=1 "
        Dim strDKNgayGoi = " 1=1 "

        If (pgtCrt.MaSo <> 0) Then
            strDKMaSo = " PGT.MaSo = @MaSo"
            cmd.Parameters.Add("@MaSo", OleDbType.Integer)
            cmd.Parameters("@MaSo").Value = pgtCrt.MaSo
        End If
        If (pgtCrt.ChkTenKH) Then
            strDKTenKH = " KH.TenKhachHang like @TenKhachHang "
            cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
            cmd.Parameters("@TenKhachHang").Value = "%" + pgtCrt.TenKH + "%"
        End If
        If (pgtCrt.ChkNgayGoi) Then
            strDKSoTienGoi = " PGT.NgayGoi between @NgayGoiTu and @NgayGoiDen "
            cmd.Parameters.Add("@NgayGoiTu", OleDbType.Date)
            cmd.Parameters("@NgayGoiTu").Value = pgtCrt.NgayGoiTu
            cmd.Parameters.Add("@NgayGoiDen", OleDbType.Date)
            cmd.Parameters("@NgayGoiDen").Value = pgtCrt.NgayGoiDen

        End If
        If (pgtCrt.SoTienGoiTu) Then
            strDKSoTienGoi = " PGT.SoTienGoi between @SoTienGoiTu and @SoTienGoiDen "
            cmd.Parameters.Add("@SoTienGoiTu", OleDbType.Double)
            cmd.Parameters("@SoTienGoiTu").Value = pgtCrt.SoTienGoiTu
            cmd.Parameters.Add("@SoTienGoiDen", OleDbType.Double)
            cmd.Parameters("@SoTienGoiDen").Value = pgtCrt.SoTienGoiDen

        End If

        Dim strDKWhere As String = " Where "
        strDKWhere += "PGT.MaSo=STK.MaSo and STK.MaKhachHang=KH.MaKhachHang and "
        strDKWhere += strDKMaSo
        strDKWhere += " and " + strDKTenKH
        strDKWhere += " and " + strDKSoTienGoi
        strDKWhere += " and " + strDKNgayGoi

        Dim strSQL As String = "Select PGT.MaSo,KH.TenKhachHang,PGT.SoTienGoi,PGT.NgayGoi From PhieuGoiTien PGT,SoTietKiem STK,KhachHang KH"
        strSQL += strDKWhere
        strSQL += " Order by KH.TenKhachHang "

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd

    End Function
    'Goi ham tim kiem
    Public Function TimKiem(ByVal pgtCrt As PhieuGoiTienCrt) As List(Of PhieuGoiTienCrt)
        Dim dt As New DataTable()
        Dim con As OleDbConnection
        con = DataProvider.ConnectDB()
        Dim cmd As OleDbCommand = BuildQuery(pgtCrt, con)

        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim dspgt As New List(Of PhieuGoiTienCrt)()
        While (reader.Read())
            Dim _pgtCrt As New PhieuGoiTienCrt()
            _pgtCrt.MaSo = reader.GetInt32(0)
            _pgtCrt.TenKH = reader.GetString(1)
            _pgtCrt.SoTienGoi = reader.GetDouble(2)
            _pgtCrt.NgayGoi = reader.GetDateTime(3)
            dspgt.Add(_pgtCrt)
        End While
        con.Close()
        Return dspgt
    End Function
    'Lấy thông tin phiếu gửi tiền theo ma phieu goi
    Public Function LayThongTinPhieuGoi(ByVal maphieugoi As Integer) As DataTable
        Dim dt As New DataTable()
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT PGT.MaSo,PGT.NgayGoi,PGT.SoTienGoi,KH.TenKhachHang From PhieuGoiTien PGT, SoTietKiem STK, KhachHang KH Where PGT.MaSo=STK.MaSo And STK.MaKhachHang=KH.MaKhachHang And PGT.MaSo=" + maphieugoi.ToString()
        Dim comand As New OleDbCommand(strSQL, cnn)
        Dim Adapter As New OleDbDataAdapter()
        Adapter.SelectCommand = comand
        Adapter.Fill(dt)
        cnn.Close()
        Return dt
    End Function
    'Ham cap nhat phieu goi tien
    Public Function CapNhatPhieuGoiTien(ByVal pgtDTO As PhieuGoiTienDTO) As Integer
        Dim sql As String = "Update PHIEUGOITIEN Set MaSo=?,NgayGoi=?,SoTienGoi=? Where MaPhieuGoi=" + pgtDTO.MaPhieuGoi.ToString()
        Dim coon As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, coon)
        Dim para As OleDbParameter

        para = cmd.Parameters.Add("@MaSo", OleDbType.Integer)
        para.Value = pgtDTO.MaSo

        para = cmd.Parameters.Add("@NgayGoi", OleDbType.DBDate)
        para.Value = pgtDTO.NgayGoi 

        para = cmd.Parameters.Add("@SoTienGoi", OleDbType.Double)
        para.Value = pgtDTO.SoTienGoi

        Dim i As Integer = cmd.ExecuteNonQuery()
        coon.Close()
        If i >= 0 Then
            Return 1
        Else
            Return 0
        End If
    End Function

    Public Function LayThongTinDanhSachPhieuGuiTien() As DataTable
        Dim dt As New DataTable()
        Dim strSQl As String = "select pg.MaPhieuGoi,pg.MaSo,kh.MaKhachHang,kh.TenKhachHang,pg.SoTienGoi,pg.NgayGoi "
        strSQl += " FROM SOTIETKIEM tk,PHIEUGOITIEN pg,KHACHHANG kh"
        strSQl += "  WHERE tk.MASO = pg.MASO And kh.MAKHACHHANG = tk.MAKHACHHANG"
        strSQl += " GROUP BY pg.MaPhieuGoi,pg.MaSo,kh.MaKhachHang,kh.TenKhachHang,pg.SoTienGoi,pg.NgayGoi "
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim Adapter As New OleDbDataAdapter(strSQl, cnn)
        Adapter.Fill(dt)
        cnn.Close()
        Return dt
    End Function

    Public Function ImportDanhSachTuExcel(ByVal str As String) As DataTable
        Dim dt As New DataTable()
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.KetNoiExcel(str)
        Dim comand As New OleDbCommand()
        Dim strSql = "select * from [Sheet1$]"
        comand.Connection = cnn
        comand.CommandText = strSql
        Dim adapter As New OleDbDataAdapter(comand)
        adapter.Fill(dt)
        cnn.Close()
        Return dt
    End Function

    Public Function TongThu(ByVal _date As Date)
        Dim dt As New DataTable()
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT stk.MaLOaiTietKiem,Sum(pgt.SOTIENGOI) as TongThu FROM PHIEUGOITIEN pgt,SOTIETKIEM stk "
        strSQL += " WHERE pgt.NgayGoi = @ngay And pgt.MaSo = stk.MaSo"
        strSQL += " Group By stk.MaLOaiTietKiem"
        Dim comand As New OleDbCommand(strSQL, cnn)
        Dim para As OleDbParameter
        para = comand.Parameters.Add("@ngay", OleDbType.DBDate)
        para.Value = _date.Date
        Dim Adpater As New OleDbDataAdapter(comand)
        Adpater.Fill(dt)
        cnn.Close()
        Return dt
    End Function
End Class
